'''
Created on Oct 28, 2010

@author: linh-cl
'''
from pysqlite2 import dbapi2 as sqlite
from core.Category import Category 

class CategoryManager( object ):
  def __init__ ( self, conn = None ):
    self.conn = conn
  '''
  Get all category from database and store them into dictionary
  '''
  def getAllCategories( self ):
    cats = []
    cur = self.conn.cursor()
    cur.execute( "SELECT cID,cName,cParent from tbCategories ORDER BY cName" )
    for ( id, name, parent ) in cur:
      cat = Category( id, name, parent )
      cats.append( cat )
    cur.fetchall()
    return cats

  def getCategory( self, id ):
    cur = self.conn.cursor()
    param = ( id, )
    cur.execute( 'SELECT cName, cParent from tbCategories where cID=?', param )
    ret = cur.fetchone()
    if ret == None:
      return None
    else:
      return Category( id, ret[0], ret[1] )
  def getCategoryByName( self, name ):
    cur = self.conn.cursor()
    param = ( name, )
    cur.execute( 'SELECT cID, cParent from tbCategories WHERE cName=?', param )
    ret = cur.fetchone()
    if ret == None:
      return None
    else:
      return Category( ret[0], name, ret[1] )
  def checkCategory( self, catName ):
    if None == self.getCategoryByName( catName ):
      return False
    else:
      return True

  def getCategoryId( self, catName ):
    cat = self.getCategoryByName( catName )
    if cat == None:
      return 0
    else:
      return cat.get_id()
  def updateCategory( self, cat ):
    cur = self.conn.cursor()
    param = ( cat.get_name(), cat.get_parent(), cat.get_id(), )
    cur.execute( "UPDATE tbCategories SET cName=?, cParent=? WHERE cID=?", param )
    self.conn.commit()

  def insertCategory( self, cat ):
    cur = self.conn.cursor()
    param = ( cat.get_name(), cat.get_parent(), )
    cur.execute( "INSERT INTO tbCategories (cName, cParent) VALUES (?, ?)", param )
    self.conn.commit()
  def deleteCategory( self, cat ):
    cur = self.conn.cursor()
    param = ( cat.get_id(), )
    cur.execute( "DELETE FROM tbCategories WHERE cID=?", param )
    self.conn.commit()
  def getCategoryLevel0( self ):
    cats = []
    cur = self.conn.cursor()
    cur.execute( "SELECT cID,cName,cParent from tbCategories WHERE cParent=0 OR cParent=cID ORDER BY cName" )
    for ( id, name, parent ) in cur:
      cat = Category( id, name, parent )
      cats.append( cat )
    cur.fetchall()
    return cats
  def getChildCategory( self, cat ):
    cats = []
    cur = self.conn.cursor()
    params = ( cat.get_id(), )
    cur.execute( "SELECT cID,cName,cParent from tbCategories WHERE cParent=? ORDER BY cName", params )
    for ( id, name, parent ) in cur:
      cat = Category( id, name, parent )
      cats.append( cat )
    if len( cats ) == 0:
      return None
    else:
      return cats
  def moveToLevel0( self, cat ):
    cur = self.conn.cursor()
    params = ( cat.get_id(), )
    cur.execute( "UPDATE tbCategories SET cParent=0 WHERE cID=?", params )
    self.conn.commit()
  def moveToChildOf( self, cat, parrentCat ):
    cur = self.conn.cursor()
    params = ( parrentCat.get_id(), cat.get_id() )
    cur.execute( "UPDATE tbCategories SET cParent=? WHERE cID=?", params )
    self.conn.commit()
